import xlwt;
import xlrd;
from xlutils.copy import copy;

import sqlite3
import sys
import time

#2011-01-01 01:01:01
starttime = time.mktime((2011, 1, 1, 1, 1, 1, 0, 0, 0))
#2018-01-01 01:01:01
endtime = time.mktime((2018, 1, 1, 1, 1, 1, 0, 0, 0))

db = sqlite3.connect("finance.sqlite")
codes = [
            "000001",
            "002142",
            "600000",
            "600015",
            "600016",
            "600036",
            "601009",
            "601166",
            "601169",
            "601288",
            "601328",
            "601398",
            "601818",
            "601939",
            "601988",
            "601998",
            ]

def insertRowPrice_(timest):
    prices = {}
    for code in codes:
        sql = "select price from price where code=\"" + code +"\" " + " and " + " time=\"" + time.strftime("%Y-%m-%d", time.gmtime(timest))+"\""
        for row in db.cursor().execute(sql):
            prices[code] = row[0]

    xlsrow = []
    if prices :
        for code in codes:
            xlsrow.append("")
            xlsrow.append("")
            xlsrow.append(time.strftime("%Y/%m/%d", time.gmtime(timest)))
            if code in prices:
                xlsrow.append(prices[code])
            else:
                xlsrow.append("")
            xlsrow.append("")

    return xlsrow

def writeXls(filename):
    wb = xlwt.Workbook();
    ws = wb.add_sheet('sheetName');

    i = 0
    for i_time in range(int(endtime), int(starttime), -24*60*60):
        pricedata = insertRowPrice_(i_time)
	print pricedata
        if pricedata == []:
            continue
        for j in range(len(pricedata)):
            ws.write(i, j, pricedata[j]);
	i+=1
    wb.save('test.xls')


if __name__ == "__main__":
 #   insertRowPrice()
   # insertRowPrice_("2017-11-29")
    writeXls("test.xls")
    sys.exit()

oldWb = xlrd.open_workbook("old.xls", formatting_info=True);
oldWbS = oldWb.sheet_by_index(0)
newWb = copy(oldWb);
newWs = newWb.get_sheet(0);
inserRowNo = 1
for i in range (0, 80):
    newWs.write(inserRowNo, i, "");
 
for rowIndex in range(inserRowNo, oldWbS.nrows):
    for colIndex in range(oldWbS.ncols):
        newWs.write(rowIndex + 1, colIndex, oldWbS.cell(rowIndex, colIndex).value);
newWb.save('fileName.xls');
